﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace AdministracionCooperativa.Formularios
{
    public partial class Frmco : Form
    {
        public Frmco()
        {
            InitializeComponent();
        }

        private void Frmco_Load(object sender, EventArgs e)
        {
            dat.Text = DateTime.Today.ToShortDateString();//DateTime.Now.ToString();

        }
        private string ObtenerSQL()
        {
            StringBuilder consulta = new StringBuilder();
            var  fechaHoy = DateTime.Today;
            consulta.Append("INSERT INTO TBLREG(FECHA,FOLIO,SYSUSR_IDUSR,CATUNI_NUMUNI,CUOTA,SEGCHO,");
            consulta.Append("CHECADOR,SUBBAR,POSTURERO,SEGIMSS,TEMP,COOSOC,PAGADO,FECPAG)");
            consulta.Append(" select DATE_FORMAT(curdate(),'%Y%-%c%-%d') as FECHA, null as folio,");
            consulta.Append("1 as SYSUSR_IDUSR, b.NUMUNI as CATUNI_NUMUNI, CASE ");
            consulta.Append("when (select CATUNI_NUMUNI from tbltaller where tbltaller.CATUNI_NUMUNI=b.NUMUNI and");
            consulta.Append(" tbltaller.FECFIN is null) is null ");
            consulta.Append("then a.CUOTA else 0 end as C,");
            consulta.Append(" a.SEGCHO,a.CHECADOR,a.SUBBAR,a.POSTURERO,a.SEGIMSS,a.TEMP,a.COOSOC,");
            consulta.Append(" 0 as PAGADO, null AS FECPAG");
            consulta.Append(" from catcou a inner join catuni b on a.idCOU=b.CATCOU_idCOU");
            consulta.Append(" and b.NUMUNI not in(select numuni from catuni c inner join tblreg t on c.NUMUNI=t.CATUNI_NUMUNI and t.FECHA=");
            consulta.Append("DATE_FORMAT(curdate(),'%Y%-%c%-%d'))");
           
            return consulta.ToString();
        }
        private void button3_Click(object sender, EventArgs e)
        {
            MySqlConnection cnn = new MySqlConnection();
            cnn.ConnectionString = "Server=localhost; Database=combis; UID=root; PWD=root";
            cnn.Open();
            MySqlCommand comando = new MySqlCommand();
            comando.Connection = cnn;
            comando.CommandType = CommandType.Text;
            comando.CommandText = ObtenerSQL();
            var devuelto = comando.ExecuteNonQuery();
            MessageBox.Show(devuelto.ToString());
        }
        
    }
}
